<?php
    //Agent Class define
    include("ClassAgent.php");

    //Link to MySql
    include("mysql_connect.php");

// Load File
$xml = simplexml_load_file("ListOfAgent.xml");
	
	// Read File
	foreach($xml->children()->children()->children() as $child)
	{	// Read Data For Each
		$OneAgent = new Agent;
		$OneAgent->AddItemFromXML($child);
		//$OneAgent->PrintElement();
		
		$strSqlCommand = "SELECT * FROM REAgent ";
		$strSqlCommand .= "WHERE AgentID = '".$OneAgent->AgentID."' ";
		$result = mysql_query($strSqlCommand);
		if( @mysql_num_rows($result) == 0)
		{// 1. if AgentID Not Exist => Add
			// Check Sub Table
			$strSqlCommand = "SELECT idREOffice FROM REOffice ";
			$strSqlCommand .= "WHERE OfficeID= '". $OneAgent->OfficeID."' ";
			
			$result = mysql_query($strSqlCommand);
			if( @mysql_num_rows($result) == 0)
			{// 1.1 if Not Exist => Add & get id
				echo $strSqlCommand ." 1.Office ID Does not Exist<br/>";
				continue;
			}
			else
			{// 1.2 else Get idSubTable
				$rowOffice = @mysql_fetch_row($result);
				$idOffice = $rowOffice[0];
			}

			// Check Sub Table
			$strSqlCommand = "SELECT idContactInformation FROM ContactInformation ";
			$strSqlCommand .= "WHERE OfficePhone = '". $OneAgent->ContactInfo->OfficePhone."' ";
			$strSqlCommand .= "AND CellPhone = '".$OneAgent->ContactInfo->CellPhone."' ";
			$strSqlCommand .= "AND HomePhone = '".$OneAgent->ContactInfo->HomePhone."' ";
			$strSqlCommand .= "AND Fax = '".$OneAgent->ContactInfo->Fax."' ";
			$strSqlCommand .= "AND Pager = '".$OneAgent->ContactInfo->Pager."' ";
			$strSqlCommand .= "AND Email = '".$OneAgent->ContactInfo->Email."' ";
			$strSqlCommand .= "AND URL = '".$OneAgent->ContactInfo->URL."' ";
			
			$result = mysql_query($strSqlCommand);
			if( @mysql_num_rows($result) == 0)
			{// 1.1 if Not Exist => Add & get id
				$strSqlCommand = "INSERT INTO ContactInformation( OfficePhone, CellPhone, HomePhone, Fax, Pager, Email, URL) ";
				$strSqlCommand .= "VALUES( '".$OneAgent->ContactInfo->OfficePhone."', ";
				$strSqlCommand .= "'".$OneAgent->ContactInfo->CellPhone."', ";
				$strSqlCommand .= "'".$OneAgent->ContactInfo->HomePhone."', ";
				$strSqlCommand .= "'".$OneAgent->ContactInfo->Fax."', ";
				$strSqlCommand .= "'".$OneAgent->ContactInfo->Pager."', ";
				$strSqlCommand .= "'".$OneAgent->ContactInfo->Email."', ";
				$strSqlCommand .= "'".$OneAgent->ContactInfo->URL."')";
				
				mysql_query($strSqlCommand);
				$idContact = mysql_insert_id();
			}
			else
			{// 1.2 else Get idSubTable
				$rowContactInformation = @mysql_fetch_row($result);
				$idContact = $rowContactInformation[0];
			}


			// Add Main Table
			$strSqlCommand = "INSERT INTO REAgent( FirstName, LastName, ContactInformation_idContactInformation, Address, AgentID, REOffice_idREOffice) ";
			$strSqlCommand .= "VALUES( '".$OneAgent->FirstName."', ";
			$strSqlCommand .= "'".$OneAgent->LastName."', ";
			$strSqlCommand .= $idContact.", ";
			$strSqlCommand .= "'".$OneAgent->Address."', ";
			$strSqlCommand .= "'".$OneAgent->AgentID."', ";
			$strSqlCommand .= $idOffice." )";
			
			mysql_query($strSqlCommand);
		}
		else
		{// 2. else Check Detial
			$rowAgent = @mysql_fetch_array($result);

			// 2.1 Check Sub Table (modify or bypass)
			$strSqlCommand = "SELECT idREOffice FROM REOffice WHERE OfficeID = '" . $OneAgent->OfficeID."'";
			$result = mysql_query($strSqlCommand);
				
			if( @mysql_num_rows($result) == 0)	//100
			{
				echo $strSqlCommand ." 2.Office ID Does not Exist<br/>"; 
				continue;
			}
			else
			{
				$rowOffice = @mysql_fetch_array($result);
				$idOffice = $rowOffice[0];
			}

			$strSqlCommand = "SELECT * FROM ContactInformation WHERE idContactInformation = " . $rowAgent["ContactInformation_idContactInformation"];
			$result = mysql_query($strSqlCommand);
			$rowContactInformation = @mysql_fetch_array($result);
				
			if(strcmp($rowContactInformation["OfficePhone"]	, $OneAgent->ContactInfo->OfficePhone) == 0 
			&& strcmp($rowContactInformation["CellPhone"]	, $OneAgent->ContactInfo->CellPhone) == 0
			&& strcmp($rowContactInformation["HomePhone"]	, $OneAgent->ContactInfo->HomePhone) == 0
 			&& strcmp($rowContactInformation["Fax"] 	, $OneAgent->ContactInfo->Fax) == 0
 			&& strcmp($rowContactInformation["Pager"] 	, $OneAgent->ContactInfo->Pager) == 0
 			&& strcmp($rowContactInformation["Email"] 	, $OneAgent->ContactInfo->Email) == 0
 			&& strcmp($rowContactInformation["URL"] 	, $OneAgent->ContactInfo->URL) == 0 )
			{//bypass
				$bIsModify = false;
			}
			else
			{
				$strSqlCommand =  "UPDATE ContactInformation SET OfficePhone='".$OneAgent->ContactInfo->OfficePhone;
				$strSqlCommand .= "', CellPhone='".$OneAgent->ContactInfo->CellPhone;
				$strSqlCommand .= "', HomePhone='".$OneAgent->ContactInfo->HomePhone;
				$strSqlCommand .= "', Fax='".$OneAgent->ContactInfo->Fax;
				$strSqlCommand .= "', Pager='".$OneAgent->ContactInfo->Pager;
				$strSqlCommand .= "', Email='".$OneAgent->ContactInfo->Email;
				$strSqlCommand .= "', URL='".$OneAgent->ContactInfo->URL;
				$strSqlCommand .= "' WHERE idContactInformation = ".$rowAgent["ContactInformation_idContactInformation"];
				
				mysql_query($strSqlCommand);
				$bIsModify = true;
			}			
			// 2.2 Check Detail (modify or bypass)	
			if(strcmp($rowAgent["FirstName"], $OneAgent->FirstName) == 0 
			&& strcmp($rowAgent["LastName"]	, $OneAgent->LastName) == 0
			&& $bIsModify == false
 			&& strcmp($rowAgent["Address"] 	, $OneAgent->Address) == 0
 			&& strcmp($rowAgent["AgentID"] 	, $OneAgent->AgentID) == 0
 			&& strcmp($rowAgent["REOffice_idREOffice"] , $idOffice ) == 0
				)
			{//bypass
			}
			else
			{//idREAgent=".$rowAgent["idREAgent"];
				//$strSqlCommand .= ", 
				$strSqlCommand =  "UPDATE REAgent SET FirstName='".$OneAgent->FirstName;
				$strSqlCommand .= "', LastName='".$OneAgent->LastName;
				$strSqlCommand .= "', Address='".$OneAgent->Address;
				$strSqlCommand .= "', AgentID='".$OneAgent->AgentID;
				$strSqlCommand .= "', REOffice_idREOffice='".$idOffice ;
				$strSqlCommand .= "' WHERE idREAgent = ".$rowAgent["idREAgent"];
				mysql_query($strSqlCommand);
			}
		}
	}
?>